package com.zhongwang.mms.util;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;

/**
 * @ClassName: ExportExcelUtils
 * @Description: Excel导出工具类
 * @Author: 徐一贺
 * @CreateDate: 2021-7-12 09:34:47
 * @Phone: 18241927380
 * @Version: 1.0.0
 */
public class ExportExcelUtils {
    /**
     * @param path          生成的文件夹路径
     * @param excelName     生成的Excel文件名
     * @param tempTableName 刚刚生成的临时表的名字
     * @param response      向客户端响应对象
     * @return 导出是否成功
     * @throws IOException 输入输出流异常
     * @ClassName: Export
     * @Description: Excel导出方法
     * @Author: 徐一贺
     * @CreateDate: 2021-7-12 09:34:54
     * @Phone: 18241927380
     * @Version: 1.0.0
     */
    public static boolean Export(String path, String excelName, String tempTableName, HttpServletResponse response, String active) throws IOException, InterruptedException {
        //数据库IP
        String ip = "";
        //数据库名称
        String dataBaseName = "";
        //用户名
        String username = "";
        //密码
        String password = "";
        if (active.equals("pro")) {
            ip = "192.168.50.5";
            dataBaseName = "DB_MMS";
            username = "sa";
            password = "zw1234aA#";
        } else if (active.equals("test")) {
            ip = "192.168.72.8";
            dataBaseName = "DB_MMS";
            username = "sa";
            password = "zw1234aA#";
        } else if (active.equals("dev")) {
            ip = "192.168.72.8";
            dataBaseName = "DB_MMS";
            username = "sa";
            password = "zw1234aA#";
        } else if (active.equals("bug")) {
            ip = "192.168.72.8";
            dataBaseName = "DB_MMS_BUG";
            username = "sa";
            password = "zw1234aA#";
        }
        //结果
        boolean result = false;
        //excel文件路径
        String excelPath = path + excelName;
        //导出完成标识文件路径
        String endPath = path + "导出完成.txt";
        //检测之前的文件如果存在则删除
        File beforeFile = new File(excelPath);
        if (beforeFile.exists()) {
            //删除存在的文件
            beforeFile.delete();
        }
        //创建导出使用的文件夹
        File folderPath = new File(path);
        //创建文件夹
        folderPath.mkdirs();
        //导出的命令
        String command = "cmd /c E:\\ExportExcelTool\\ExportExcelTool.exe " + ip + " " + dataBaseName + " " + username + " " + password + " SELECT◕‿◕*◕‿◕FROM◕‿◕" + tempTableName + "◕‿◕order◕‿◕by◕‿◕'序号' " + excelPath + " " + endPath;
        //执行命令
        Process process = Runtime.getRuntime().exec(command);
        //检测完成标识文件是否存在，即excel生成完成，如果生成完成，则删除完成标识文件。
        File endFile = new File(endPath);
        //循环判断是否未生成
        while (true) {
            //如果生成则设置结果为true
            if (endFile.exists()) {
                //删除导出完成标识文件
                endFile.delete();
                //设置结果为true
                result = true;
//                FileUtils.downloadFile(excelPath, response);
                break;
            }
            Thread.sleep(100);
        }
//        //如果生成则设置结果为true,为什么会出现第二遍、避免上个循环无法进入。
//        if (endFile.exists()) {
//            //删除导出完成标识文件
//            endFile.delete();
//            //设置结果为true
//            result = true;
//        }
//        //如果生成完成，则下载生成的文件。
//        if (result) {
//            //下载Excel文件
//            FileUtils.downloadFile(excelPath, response);
//        }
        return result;
    }

    /**
     * @param path          生成的文件夹路径
     * @param excelName     生成的Excel文件名
     * @param tempTableName 刚刚生成的临时表的名字
     * @param active        激活的模式
     * @param IsOrder       是否进行对序号字段排序
     * @return 导出是否成功
     * @throws IOException 输入输出流异常
     * @ClassName: Export
     * @Description: Excel导出方法
     * @Author: 徐一贺
     * @CreateDate: 2021-7-12 09:34:54
     * @Phone: 18241927380
     * @Version: 1.0.0
     */
    public static boolean BuildExcel(String path,
                                     String excelName,
                                     String tempTableName,
                                     String active,
                                     Boolean IsOrder
    ) throws IOException, InterruptedException {
        //数据库IP
        String ip = "";
        //数据库名称
        String dataBaseName = "";
        //用户名
        String username = "";
        //密码
        String password = "";
        if (active.equals("pro")) {
            ip = "192.168.50.5";
            dataBaseName = "DB_MMS";
            username = "sa";
            password = "zw1234aA#";
        } else if (active.equals("test")) {
            ip = "192.168.72.8";
            dataBaseName = "DB_MMS";
            username = "sa";
            password = "zw1234aA#";
        } else if (active.equals("dev")) {
            ip = "192.168.72.8";
            dataBaseName = "DB_MMS";
            username = "sa";
            password = "zw1234aA#";
        } else if (active.equals("bug")) {
            ip = "192.168.72.8";
            dataBaseName = "DB_MMS_BUG";
            username = "sa";
            password = "zw1234aA#";
        }
        //结果
        boolean result = false;
        //excel文件路径
        String excelPath = path + excelName;
        //导出完成标识文件路径
        String endPath = path + "导出完成.txt";
        //检测之前的文件如果存在则删除
        File beforeFile = new File(excelPath);
        if (beforeFile.exists()) {
            //删除存在的文件
            beforeFile.delete();
        }
        //创建导出使用的文件夹
        File folderPath = new File(path);
        //创建文件夹
        folderPath.mkdirs();
        String sql = "";
        if (IsOrder) {
            sql = " SELECT◕‿◕*◕‿◕FROM◕‿◕" + tempTableName + "◕‿◕order◕‿◕by◕‿◕'序号' ";
        } else {
            sql = " SELECT◕‿◕*◕‿◕FROM◕‿◕" + tempTableName + "◕‿◕ ";
        }
        //导出的命令
        String command = "cmd /c E:\\ExportExcelTool\\ExportExcelTool.exe " + ip + " " + dataBaseName + " " + username + " " + password + sql + excelPath + " " + endPath;
        try {
            //执行命令
            Process process = Runtime.getRuntime().exec(command);
            //检测完成标识文件是否存在，即excel生成完成，如果生成完成，则删除完成标识文件。
            File endFile = new File(endPath);
            //循环判断是否未生成
            while (true) {
                //如果生成则设置结果为true
                if (endFile.exists()) {
                    //删除导出完成标识文件
                    endFile.delete();
                    //设置结果为true
                    result = true;
                    break;
                }
                Thread.sleep(100);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}
